import flask
from flask import Flask, request, jsonify
from flask import url_for  # 进行网页跳转
import os  # 用于操作系统文件的依赖库
import re  # 引入正则表达式对用户输入进行限制
import pymysql  # 连接数据库
import csv
from io import TextIOWrapper
from flask import jsonify
from io import StringIO
from flask import Flask, render_template, request, redirect, url_for
from flask import Flask, render_template, request, jsonify
from sparkai.llm.llm import ChatSparkLLM, ChunkPrintHandler
from sparkai.core.messages import ChatMessage
# 初始化
app = Flask(__name__, static_folder='static')
app = flask.Flask(__name__)
# 初始化数据库连接
# 使用pymysql.connect方法连接本地mysql数据库
db = pymysql.connect(host='127.0.0.1', port=3306, user='root',
                     password='wsd123wsd', database='admins', charset='utf8')
# 操作数据库，获取db下的cursor对象
cursor = db.cursor()
# 存储登陆用户的名字用户其它网页的显示
users = []
class MyLLM(object):

    def __init__(self):
        # 星火认知大模型Spark3.5 Max的URL值，其他版本大模型URL值请前往文档（https://www.xfyun.cn/doc/spark/Web.html）查看
        SPARKAI_URL = "wss://spark-api.xf-yun.com/v3.5/chat"
        # 星火认知大模型调用秘钥信息，请前往讯飞开放平台控制台（https://console.xfyun.cn/services/bm35）查看
        SPARKAI_APP_ID = "bed37ff5"
        SPARKAI_API_SECRET = "NzMyZmVmMWZmNjEzMDAzNmVmN2ZhZWY4"
        SPARKAI_API_KEY = "82c17e67351e863a3eb78d14fc0e41d9"
        # 星火认知大模型Spark3.5 Max的domain值，其他版本大模型domain值请前往文档（https://www.xfyun.cn/doc/spark/Web.html）查看
        SPARKAI_DOMAIN = "generalv3.5"
        self.spark = ChatSparkLLM(
            spark_api_url=SPARKAI_URL,
            spark_app_id=SPARKAI_APP_ID,
            spark_api_key=SPARKAI_API_KEY,
            spark_api_secret=SPARKAI_API_SECRET,
            spark_llm_domain=SPARKAI_DOMAIN,
            streaming=False,
        )
        self.history = []

    def info(self, content="", role="user", code=""):
        messages = [
            ChatMessage(role="system", content=code)
        ]
        for que, ans in self.history:
            messages.append(ChatMessage(role="user", content=que))
            messages.append(ChatMessage(role="assistant", content=ans))

        messages.append(ChatMessage(role="user", content=content))

        handler = ChunkPrintHandler()
        reinfo = self.spark.generate([messages], callbacks=[handler])
        self.history.append((content, reinfo.generations[0][0].text))
        return reinfo.generations[0][0].text

    def reset(self):
        self.history = []


model = MyLLM()
with open("code.txt", "r", encoding='utf-8') as f:
    code = f.read()

@app.route("/")#主页
def index1():
    return render_template('index.html')
@app.route("/index2")#主页2
def index2():
    return render_template('index2.html')


@app.route('/ask1', methods=['POST'])#对话框1
def ask1():
    user_input = request.json.get('codeArea')
    if user_input:
        answerArea = model.info(user_input, code=code)
        return jsonify({'answerArea': answerArea})
    return jsonify({'answerArea': '请输入有效的问题'})


@app.route('/ask2', methods=['POST'])#对话框1
def ask2():
    user_input = request.json.get('questionArea')
    if user_input:
        answerArea = model.info(user_input, code=code)
        return jsonify({'answerArea': answerArea})
    return jsonify({'answerArea': '请输入有效的问题'})





@app.route("/login", methods=["GET", "POST"])#登录
def login():
    # 增加会话保护机制(未登陆前login的session值为空)
    flask.session['login'] = ''
    if flask.request.method == 'POST':
        user = flask.request.values.get("user", "")
        pwd = flask.request.values.get("pwd", "")
        # 防止sql注入,如:select * from admins where admin_name = '' or 1=1 -- and password='';
        # 利用正则表达式进行输入判断
        result_user = re.search(r"^[a-zA-Z]+$", user)  # 限制用户名为全字母
        result_pwd = re.search(r"^[a-zA-Z\d]+$", pwd)  # 限制密码为 字母和数字的组合
        if result_user != None and result_pwd != None:  # 验证通过
            msg = '用户名或密码错误'
            # 正则验证通过后与数据库中数据进行比较
            sql = "select * from admins where admin_name='" + \
                user + "' and admin_password='" + pwd + "';"
            cursor.execute(sql)
            result = cursor.fetchone()
            # 匹配得到结果即管理员数据库中存在此管理员
            if result:
                # 登陆成功
                flask.session['login'] = 'OK'

                return (render_template('index2.html'))

        else:  # 输入验证不通过
            msg = '非法输入'
    else:
        msg = ''
        user = ''
    return flask.render_template('login.html', msg=msg, user=user)


@app.route("/logout")#登出
def logout():
    # 清除会话中的登录状态
    flask.session.pop('login', None)
    # 或者使用下面这种方式清除所有会话数据
    # flask.session.clear()

    # 重定向到登录页面或者其他适当的页面
    return flask.redirect(flask.url_for('login'))


@app.route("/About Us")
def about():
    return render_template('About Us.html')
@app.route("/About Us2")
def about2():
    return render_template('About Us2.html')

@app.route('/One-on-one Tutoring', methods=['GET','POST'])
def One():
    return render_template('One-on-one Tutoring.html')
@app.route('/One-on-one Tutoring2', methods=['GET','POST'])
def One2():
    return render_template('One-on-one Tutoring2.html')



@app.route("/Practical Courses")
def courses():
    return render_template('Practical Courses.html')
@app.route("/Practical Courses2")
def courses2():
    return render_template('Practical Courses2.html')


@app.route('/student', methods=['GET', "POST"])
def student():
    # login session值
    if flask.session.get("login", "") == '':
        # 用户没有登陆
        print('用户还没有登陆!即将重定向!')
        return flask.redirect('/')
    insert_result = ''
    # 当用户登陆有存储信息时显示用户名,否则为空
    if users:
        for user in users:
            user_info = user
    else:
        user_info = ''
    # 获取显示管理员数据信息(GET方法的时候显示数据)
    if flask.request.method == 'GET':
        sql_list = "select * from students_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    if flask.request.method == 'POST':
        # 获取输入的学生信息
        student_id = flask.request.values.get("student_id", "")
        student_class = flask.request.values.get("student_class", "")
        student_name = flask.request.values.get("student_name", "")
        print(student_id, student_class, student_name)

        try:
            # 信息存入数据库
            sql = "create table if not exists students_infos(student_id varchar(15) primary key,student_class varchar(20),student_name varchar(10));"
            cursor.execute(sql)
            sql_1 = "insert into students_infos(student_id, student_class, student_name)values(%s,%s,%s)"
            cursor.execute(sql_1, (student_id, student_class, student_name))
            # result = cursor.fetchone()
            insert_result = "成功存入一条学生信息"
            print(insert_result)
        except Exception as err:
            print(err)
            insert_result = "学生信息插入失败"
            print(insert_result)
            pass
        db.commit()
        # POST方法时显示数据
        sql_list = "select * from students_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    return flask.render_template('student.html', insert_result=insert_result, user_info=user_info, results=results)


@app.route('/teacher', methods=['GET', "POST"])
def teacher():
    # login session值
    if flask.session.get("login", "") == '':
        # 用户没有登陆
        print('用户还没有登陆!即将重定向!')
        return flask.redirect('/')
    insert_result = ''
    # 当用户登陆有存储信息时显示用户名,否则为空
    if users:
        for user in users:
            user_info = user
    else:
        user_info = ''
    # 获取显示管理员数据信息(GET方法的时候显示数据)
    if flask.request.method == 'GET':
        sql_list = "select * from students_decision_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    if flask.request.method == 'POST':
        # 获取输入的学生选课信息
        student_id = flask.request.values.get("student_id", "")
        student_class_id = flask.request.values.get("student_class_id", "")
        teacher_id = flask.request.values.get("teacher_id", "")
        print(student_id, student_class_id, teacher_id)
        try:
            # 信息存入数据库
            sql = "create table if not exists students_decision_infos(student_id varchar(15) primary key,student_class_id varchar(20),teacher_id varchar(15),foreign key(student_id) references students_infos(student_id));"
            cursor.execute(sql)
            sql_1 = "insert into students_decision_infos(student_id, student_class_id, teacher_id)values(%s,%s,%s)"
            cursor.execute(sql_1, (student_id, student_class_id, teacher_id))
            # result = cursor.fetchone()
            insert_result = "成功存入一条选课信息"
            print(insert_result)
        except Exception as err:
            print(err)
            insert_result = "选课信息插入失败"
            print(insert_result)
            pass
        db.commit()
        # POST显示数据
        sql_list = "select * from students_decision_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    return flask.render_template('teacher.html', insert_result=insert_result, user_info=user_info, results=results)


@app.route('/grade', methods=['GET', "POST"])
def grade():
    # login session值
    if flask.session.get("login", "") == '':
        # 用户没有登陆
        print('用户还没有登陆!即将重定向!')
        return flask.redirect('/')
    insert_result = ''
    # 当用户登陆有存储信息时显示用户名,否则为空
    if users:
        for user in users:
            user_info = user
    else:
        user_info = ''
    # 获取显示管理员数据信息(GET方法的时候显示数据)
    if flask.request.method == 'GET':
        db.commit()
        sql_list = "select * from grade_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    if flask.request.method == 'POST':
        # 获取输入的学生成绩信息
        student_id = flask.request.values.get("student_id", "")
        student_class_id = flask.request.values.get("student_class_id", "")
        grade = flask.request.values.get("grade", "")
        print(student_id, student_class_id, grade)
        # 信息存入数据库
        try:
            sql = "create table if not exists grade_infos(student_id varchar(15) primary key,student_class_id varchar(20),grade tinyint unsigned,foreign key(student_id) references students_decision_infos(student_id));"
            cursor.execute(sql)
            sql_1 = "insert into grade_infos(student_id, student_class_id,grade)values(%s,%s,%s)"
            cursor.execute(sql_1, (student_id, student_class_id, grade))
            insert_result = "成功存入一条学生成绩信息"
            print(insert_result)
        except Exception as err:
            print(err)
            insert_result = "学生成绩信息插入失败"
            print(insert_result)
            pass
        db.commit()
        # POST获取数据
        sql_list = "select * from grade_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    return flask.render_template('grade.html', insert_result=insert_result, user_info=user_info, results=results)


@app.route('/grade_infos', methods=['GET', 'POST'])
def grade_infos():
    # login session值
    if flask.session.get("login", "") == '':
        # 用户没有登陆
        print('用户还没有登陆!即将重定向!')
        return flask.redirect('/')
    query_result = ''
    results = ''
    # 当用户登陆有存储信息时显示用户名,否则为空
    if users:
        for user in users:
            user_info = user
    else:
        user_info = ''
    # 获取显示管理员数据信息(GET方法的时候显示数据)
    if flask.request.method == 'GET':
        db.commit()
        sql_list = "select * from grade_infos"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    # 获取下拉框的数据
    if flask.request.method == 'POST':
        select = flask.request.form.get('selected_one')
        query = flask.request.values.get('query')
        print(select, query)
        # 判断不同输入对数据表进行不同的处理
        if select == '学号':
            try:
                sql = "select * from grade_infos where student_id = %s; "
                cursor.execute(sql, query)
                results = cursor.fetchall()
                if results:
                    query_result = '查询成功!'
                else:
                    query_result = '查询失败!'
            except Exception as err:
                print(err)
                pass
        if select == '姓名':
            try:
                sql = "select * from grade_infos where student_id in(select student_id from students_infos where student_name=%s);"
                cursor.execute(sql, query)
                results = cursor.fetchall()
                if results:
                    query_result = '查询成功!'
                else:
                    query_result = '查询失败!'
            except Exception as err:
                print(err)
                pass

        if select == '课程号':
            try:
                sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class_id=%s);"
                cursor.execute(sql, query)
                results = cursor.fetchall()
                if results:
                    query_result = '查询成功!'
                else:
                    query_result = '查询失败!'
            except Exception as err:
                print(err)
                pass

        if select == "所在班级":
            try:
                sql = "select * from grade_infos where student_class_id in(select student_class_id from students_infos where student_class=%s);"
                cursor.execute(sql, query)
                results = cursor.fetchall()
                if results:
                    query_result = '查询成功!'
                else:
                    query_result = '查询失败!'
            except Exception as err:
                print(err)
                pass

        if flask.request.values.get('query') == "":
            db.commit()
            sql_list = "select * from grade_infos"
            cursor.execute(sql_list)
            results = cursor.fetchall()
            query_result = "部分字段为空,默认查询全部数据成功"
    return flask.render_template('grade_infos.html', query_result=query_result, user_info=user_info, results=results)


@app.route('/adminstator', methods=['GET', "POST"])
def adminstator():
    # login session值
    if flask.session.get("login", "") == '':
        # 用户没有登陆
        print('用户还没有登陆!即将重定向!')
        return flask.redirect('/')
    insert_result = ''
    # 获取显示管理员数据信息(GET方法的时候显示数据)
    if flask.request.method == 'GET':
        sql_list = "select * from admins"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    # 当用户登陆有存储信息时显示用户名,否则为空
    if users:
        for user in users:
            user_info = user
    else:
        user_info = ''
    if flask.request.method == 'POST':
        # 获取输入的管理员信息
        admin_name = flask.request.values.get("admin_name", "")
        admin_password = flask.request.values.get("admin_password", "")
        #print(admin_name, admin_password)
        admin_name_result = re.search(r"^[a-zA-Z]+$", admin_name)  # 限制用户名为全字母
        admin_password_result = re.search(
            r"^[a-zA-Z\d]+$", admin_password)  # 限制密码为 字母和数字的组合
        # 验证通过
        if admin_name_result != None and admin_password_result != None:  # 验证通过
            # 获取下拉框的数据
            select = flask.request.form.get('selected_one')
            if select == '增加管理员':
                try:
                    sql = "create table if not exists admins(id int primary key auto_increment,admin_name varchar(15),admin_password varchar(20));"
                    cursor.execute(sql)
                    sql_1 = "insert into admins(admin_name,admin_password)values(%s,%s)"
                    cursor.execute(sql_1, (admin_name, admin_password))
                    insert_result = "成功增加了一名管理员"
                    print(insert_result)
                except Exception as err:
                    print(err)
                    insert_result = "增加管理员操作失败"
                    print(insert_result)
                    pass
                db.commit()
            if select == '修改管理员密码':
                try:
                    sql = "update admins set admin_password=%swhere admin_name=%s;"
                    cursor.execute(sql, (admin_password, admin_name))
                    insert_result = "管理员" + admin_name + "的密码修改成功!"
                except Exception as err:
                    print(err)
                    insert_result = "修改管理员密码失败!"
                    pass
                db.commit()
            if select == '删除管理员':
                try:
                    sql_delete = "delete from admins where admin_name='" + admin_name + "';"
                    cursor.execute(sql_delete)
                    insert_result = "成功删除管理员" + admin_name
                except Exception as err:
                    print(err)
                    insert_result = "删除管理员失败"
                    pass
                db.commit()

        else:  # 输入验证不通过
            insert_result = "输入的格式不符合要求!"
        # POST方法时显示数据
        sql_list = "select * from admins"
        cursor.execute(sql_list)
        results = cursor.fetchall()
    return flask.render_template('adminstator.html', user_info=user_info, insert_result=insert_result, results=results)


#插入数据
def insert_grade(student_id, course_id, grade):
    try:

        # 创建游标对象
        cursor = db.cursor()

        # 执行插入语句
        sql = "INSERT INTO grade_infos (student_id, student_class_id, grade) VALUES (%s, %s, %s)"
        cursor.execute(sql, (student_id, course_id, grade))

        # 提交事务
        db.commit()

        return True, "成绩插入成功"
    except Exception as e:
        # 发生异常时回滚事务
        db.rollback()
        return False, f"成绩插入失败：{str(e)}"

#处理上传文件
@app.route('/upload_grades', methods=['POST'])
def upload_grades():
    try:
        ## 从 request 中获取上传的文件
        file = flask.request.files['file']

        # 使用 StringIO 对文件进行解析
        csv_data = StringIO(file.read().decode('utf-8'))

        # 使用 CSV 模块解析文件内容
        csv_reader = csv.reader(csv_data)
        next(csv_reader)  # 跳过标题行
        print(csv_reader)
        # 逐行插入成绩信息到数据库
        for row in csv_reader:
            student_id, course_id, grade = row
            # insert data
            insert_grade(student_id, course_id, grade)
        # 返回处理结果
        return flask.render_template('result.html', success = "True",message="成绩批量导入成功")
    except Exception as e:
        return flask.render_template('result.html', success = "False",message=str(e))





# 启动服务器
app.debug = True
# 增加session会话保护(任意字符串,用来对session进行加密)
app.secret_key = 'carson'
try:
    app.run()
except Exception as err:
    print(err)
    db.close()  # 关闭数据库连接

# 启动服务器
app.debug = True
# 增加session会话保护(任意字符串,用来对session进行加密)
app.secret_key = 'carson'
try:
    app.run()
except Exception as err:
    print(err)
    db.close()  # 关闭数据库连接
if __name__ == '__main__':
    app.run()

